package com.lagou.app;

import com.lagou.entity.Dept;
import com.lagou.entity.Employee;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;

public class TestEmployee {

    // 需求1: 查询所有的员工信息 (不包含没有部门的员工)
    @Test
    public void queryAllEmployee() throws SQLException {
        // 1.获取连接
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 2.编写sql
        String sql = "select * from Employee where did is not null";
        // 3.执行sql
        List<Employee> employeeList = qr.query(sql, new BeanListHandler<>(Employee.class));
        // 4.遍历集合，得到员工信息
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
    }

    // 需求2: 查询每个员工的 姓名, 薪资 和 所属部门名称
    @Test
    public void getNameSalaryDid() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        // 1.编写sql，查询部门id存在的员工信息(部门id为空的情况下，再去查询其部门名称无意义)
        String sql = "select * from Employee where did is not null";
        // 2.查询得到employeeList
        List<Employee> employeeList = qr.query(sql, new BeanListHandler<>(Employee.class));
        for (Employee employee : employeeList) {
            // 3.遍历employeeList集合的同时，get到对应的部门id，调用findDeptNameById()方法，进而得到部门名称
                Dept dept = findDeptNameById(employee.getDid());
                // 4.将得到的Dept对象，set到Employee对象中
                employee.setDept(dept);
        }
        // 5.通过遍历employee集合，得到对应的姓名，薪资和部门信息
        for (Employee employee : employeeList) {
            System.out.println("员工姓名: " + employee.getName() + ", 员工薪资: " +
                    employee.getSalary() + ", 员工部门: " + employee.getDept().getDeptName());
        }
    }
    public Dept findDeptNameById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select deptname from dept where id = ?";
        return qr.query(sql, new BeanHandler<>(Dept.class), id);
    }
}
